package com.weixin1.dao;

import com.weixin1.bean.Employee;
import com.weixin1.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class EmployeeDao {
    private QueryRunner qr = new QueryRunner(C3p0Utils.comboPooledDataSource);
    public List<Employee> list() throws SQLException {
        return qr.query("select e_id eId,e_name eName,e_gender eGender," +
                "e_age eAge,e_birthday eBirthday,e_job eJob,e_location eLocation from employee", new BeanListHandler<>(Employee.class) );
    }

    public Employee getById(Long id) throws SQLException {
        return qr.query("select e_name eName,e_gender eGender," +
                "e_age eAge,e_birthday eBirthday,e_job eJob,e_location eLocation from employee where e_id=?", new BeanHandler<>(Employee.class), id);
    }

    public int save(Employee employee) throws SQLException {
        return qr.update("insert into employee(e_id,e_name, e_gender," +
                        "e_age,e_birthday,e_job,e_location) values(?,?,?,?,?,?,?)",employee.getEId(),employee.getEName(),
                employee.getEGender(),employee.getEAge(),employee.getEBirthday(),employee.getEJob(),employee.getELocation());
    }

    public int removeById(Long id) throws SQLException {
        return qr.update("delete from employee where e_id=?", id);
    }

    public int update(Employee employee) throws SQLException {
        return qr.update("update employee set e_name=?,e_gender=?,e_age=?,e_birthday=?,e_job=?,e_location=? where e_id=?",
                employee.getEName(),employee.getEGender(),employee.getEAge(),employee.getEBirthday(),employee.getEJob(),employee.getELocation(),employee.getEId());
    }
    //获取数据长度
    public int gettotal() throws SQLException {
        return qr.query("select count(*) from employee",new ScalarHandler<Long>()).intValue();
    }
    //分页查询
    public List<Employee> selectByPage(int page,int limit) throws SQLException {
        return qr.query("select e_id eId,e_name eName,e_gender eGender,e_age eAge,e_birthday eBirthday," +
                "e_job eJob,e_location eLocation from employee limit ?,?",new BeanListHandler<Employee>(Employee.class),(page-1)*limit,limit);
    }
}
